package dao;


import db.DbUtil;
import entity.User;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;


public class UserDao {
	public User login(String account, String pwd) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
	        conn = getConnection();
			ps = conn.prepareStatement("select * from user_info where account=? and password=?");
			ps.setObject(1, account);
			ps.setObject(2, pwd);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				User user = new User();
				user.setUserId(rs.getInt("user_id"));
				user.setAccount(rs.getString("account"));
				user.setPassword(rs.getString("password"));
				user.setRealName(rs.getString("realName"));
				user.setEmail(rs.getString("email"));
				user.setNickname(rs.getString("nickname"));
				user.setHeadImg(rs.getString("headImg"));
				user.setSex(rs.getInt("sex"));
				user.setPhone(rs.getString("phone"));
				user.setRole(rs.getInt("role"));
				if (rs.getTimestamp("create_time") != null) {
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					user.setCreateTime(sdf.format(rs.getTimestamp("create_time")));
				}
				if (rs.getTimestamp("update_time") != null) {
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					user.setUpdateTime(sdf.format(rs.getTimestamp("update_time")));
				}
				return user;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			closeConnection(ps, conn);
		}
		return null;
	}

	public int register(String account,String password,String phone,String eamil,String realName,String nickname) {
		LocalDateTime currentTime = LocalDateTime.now();
		DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
		String createTime = currentTime.format(formatter);
		int ret = DbUtil.getInstance().execute("insert into user_info(account,password,realName,email,nickname,headImg,phone,role,create_time,update_time) values(?,?,?,?,?,?,?,?,?,?)", new Object[]{account,password,realName,eamil,nickname,"backend/img/hand.jpg",phone,0,createTime,createTime});
		return ret;
	}

	public int regist(String account, String password, String realName, String nickname, Integer role) {
		LocalDateTime currentTime = LocalDateTime.now();
		DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
		String createTime = currentTime.format(formatter);
		int ret = DbUtil.getInstance().execute("insert into user_info(account,password,realName,email,nickname,headImg,phone,role,create_time,update_time) values(?,?,?,?,?,?,?,?,?,?)", new Object[]{account,password,realName,null,nickname,"backend/img/hand.jpg",null,role,createTime,createTime});
		return ret;
	}

	public List<User> queryUserPage(int start, int size, String condition, List<Object> parmas) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConnection();
			String sql = "select * from user_info where "+ condition +" limit ?,?";
			System.out.println(sql);
			ps = conn.prepareStatement(sql);
			for (int i =1;i <= parmas.size();i++){
				ps.setObject(i,parmas.get(i-1));
			}
			ps.setObject(parmas.size()+1,start);
			ps.setObject(parmas.size()+2,size);

			ResultSet rs = ps.executeQuery();
			List<User> list = new ArrayList<User>();
			while(rs.next()) {
				User user = new User();
				user.setUserId(rs.getInt("user_id"));
				user.setAccount(rs.getString("account"));
				user.setPassword(rs.getString("password"));
				user.setRealName(rs.getString("realName"));
				user.setEmail(rs.getString("email"));
				user.setNickname(rs.getString("nickname"));
				user.setHeadImg(rs.getString("headImg"));
				user.setSex(rs.getInt("sex"));
				user.setPhone(rs.getString("phone"));
				user.setRole(rs.getInt("role"));
				if (rs.getTimestamp("create_time") != null) {
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					user.setCreateTime(sdf.format(rs.getTimestamp("create_time")));
				}
				if (rs.getTimestamp("update_time") != null) {
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					user.setUpdateTime(sdf.format(rs.getTimestamp("update_time")));
				}
				list.add(user);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(ps, conn);
		}
		return null;
	}

	public Integer total_user(String condition, List<Object> parmas) {
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			conn = getConnection();
			String sql = "select count(*) from user_info where "+ condition;
			ps = conn.prepareStatement(sql);
			for (int i =1;i <= parmas.size();i++){
				ps.setObject(i,parmas.get(i-1));
			}
			ResultSet rs = ps.executeQuery();
			if(rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(ps, conn);
		}
		return 0;
	}

	public int deleteUser(Integer id) {
		int ret = DbUtil.getInstance().execute("delete from user_info where user_id=?", new Object[]{id});
		return ret;
	}

	public User select(Integer id) {
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = getConnection();
			stmt = conn.createStatement();
			String sql = "select * from user_info where user_id=" + id;
			ResultSet rs = stmt.executeQuery(sql);
			User user = null;
			if(rs.next()) {
				user = new User();
				user.setUserId(rs.getInt("user_id"));
				user.setAccount(rs.getString("account"));
				user.setPassword(rs.getString("password"));
				user.setRealName(rs.getString("realName"));
				user.setEmail(rs.getString("email"));
				user.setNickname(rs.getString("nickname"));
				user.setHeadImg(rs.getString("headImg"));
				user.setSex(rs.getInt("sex"));
				user.setPhone(rs.getString("phone"));
				user.setRole(rs.getInt("role"));
				if (rs.getTimestamp("create_time") != null) {
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					user.setCreateTime(sdf.format(rs.getTimestamp("create_time")));
				}
				if (rs.getTimestamp("update_time") != null) {
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					user.setUpdateTime(sdf.format(rs.getTimestamp("update_time")));
				}
			}
			return user;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(stmt, conn);
		}
		return null;
	}

	public int sure_user(String account) {
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = getConnection();
			stmt = conn.createStatement();
			String sql = "select count(*) from user_info where account=" + account;
			ResultSet rs = stmt.executeQuery(sql);
			if(rs.next()) {
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection(stmt, conn);
		}
		return 0;
	}


	public int update_img(User user,String headImageSrc) {
		String account = user.getAccount();
		int ret = DbUtil.getInstance().execute("update user_info set headImg=? where account=?", new Object[]{headImageSrc,account});
		return ret;
	}

	public int updateUser(String password, String realName, String email, String nickname, String headImg, String phone, String role ,String account) {
		int ret = DbUtil.getInstance().execute("update user_info set password=?,realName=?,email=?,nickname=?,headImg=?,phone=?,role=? where account=?", new Object[]{password,realName,email,nickname,headImg,phone,role,account});
		return ret;
	}

	private Connection getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/shopping?useUnicode=true&characterEncoding=utf8",
					"root", "123456");
			return conn;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	private void closeConnection(Statement stmt, Connection conn) {
		try {
			if (stmt != null) stmt.close();
			if (conn != null) conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

}
